from utils import MysqlConnectInstance, ReUtils


# 4. 清洗数据


# 清洗一行数据
def clean_data(row):
    id, key_str, value_str, from_currency, from_value, to_currency, to_value = row
    currency_parts = value_str.split("=")
    from_part = currency_parts[0]
    to_part = currency_parts[1]
    from_value, from_currency = get_currency_data(from_part)
    to_value, to_currency = get_currency_data(to_part)

    if key_str in ('USD=NGN', 'CNY=NGN'):
        to_currency = 'NGN'

    # 更新数据：
    new_row = (from_currency, from_value, to_currency, to_value, id)
    return update_clean_data(new_row)


def update_clean_data(row):
    connection = MysqlConnectInstance().get_connection()
    cursor = connection.cursor()
    update_sql = "UPDATE exchange_data_log SET from_currency = %s, from_value = %s , to_currency = %s , to_value = %s where id = %s"
    cursor.execute(update_sql, row)
    connection.commit()
    rowcount = cursor.rowcount
    cursor.close()
    return rowcount


def get_currency_data(currency_part):
    currency_value = ReUtils.extract_numbers(currency_part)[0]
    currency_code = currency_part.replace(currency_value, "")
    return currency_value, currency_code


# 查找所有待清洗数据
def query_waiting_clean_data():
    conn = MysqlConnectInstance().get_connection()
    cursor = conn.cursor()
    query = "select id, key_str, value_str, from_currency, from_value, to_currency, to_value  from exchange_data_log where from_currency = '';"
    # query = "select id, key_str, value_str, from_currency, from_value, to_currency, to_value  from exchange_data_log where to_currency = 'N';"
    cursor.execute(query)
    result = cursor.fetchall()
    return result


def main():
    #  查询所有数据
    all_waiting_clean_data = query_waiting_clean_data()
    # 清洗数据
    rowcount = 0
    for row in all_waiting_clean_data:
        rowcount = rowcount + clean_data(row)
        if rowcount % 10 == 0:
            print(f"更新进度 {rowcount / len(all_waiting_clean_data) * 100}%")
    print(f'all done! 更新 {rowcount} 条记录')


if __name__ == '__main__':
    main()
